﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Query2Object
{
    class SqlQuery:IQuery
    {
        private ISession session;

        private IQueryable queryparam;

        private int top;

        private string where;

        private List<string> DESC;
        private List<string> ASC;

        private List<string> updatecolumns;

        public SqlQuery()
        {
            where = "";
            DESC = new List<string>();
            ASC = new List<string>();
            updatecolumns = new List<string>();
        }
        #region IQuery 成员

        public ISession Session
        {
            get
            {
                return session;
            }
            set
            {
                session = value;
            }
        }

        public List<T> GetList<T>()
        {
            string sql = MakeSelectSql();
            return session.ExecuteList<T>(sql, queryparam);
        }

        public List<T> GetList<T>(int PageID, int PageSize)
        {
            string sql = MakePageSql(PageID,PageSize);
            return session.ExecuteList<T>(sql, queryparam);
        }

        public void Update()
        {
            session.ExecuteUpdate(MakeUpdateSql(), queryparam);
        }

        public void Delete()
        {
            session.ExecuteUpdate(MakeDeleteSql(), queryparam);
        }

        public int Count()
        {
            return session.ExecuteCount(MakeCountSql(), queryparam);
        }

        public IQuery From(IQueryable TableObject)
        {
            queryparam = TableObject;
            return this;
        }

        public IQuery Where(Exp exp)
        {
            where = exp.Query;
            return this;
        }

        public IQuery OrderBy(Exp exp, Direction dir)
        {
            if (dir == Direction.DESC)
            {
                DESC.Add(exp.Name);
            }
            else
            {
                ASC.Add(exp.Name);
            }
            return this;
        }
        public IQuery Top(int N)
        {
            top = N;
            return this;
        }

        private string MakeUpdateSql()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE [").Append(queryparam._ObjectTableName).Append("] SET ");
            foreach (string s in updatecolumns)
            {
                sql.Append("[").Append(s).Append("] = @").Append(s).Append(", ");
            }
            sql = sql.Remove(sql.Length - 1, 1);
            if (where != "")
            {
                sql.Append("WHERE ").Append(where);
            }
            return sql.ToString();
        }

        private string MakeDeleteSql()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM [").Append(queryparam._ObjectTableName).Append("] ");
            if (where != "")
            {
                sql.Append("WHERE ").Append(where);
            }
            return sql.ToString();
        }

        private string MakeCountSql()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(").Append(queryparam._PrimaryKeyName).Append(") FROM [").Append(queryparam._ObjectTableName).Append("] ");
            if (where != "")
            {
                sql.Append("WHERE ").Append(where);
            }
            return sql.ToString();
        }

        private string MakeSelectSql()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            if (top > 0)
            {
                sql.Append("TOP " + top).Append(" ");
            }
            sql.Append("* ").Append("FROM [").Append(queryparam._ObjectTableName).Append("] ");
            if (where != "")
            {
                sql.Append("WHERE ").Append(where);
            }
            if ((DESC.Count + ASC.Count) > 0)
            {
                sql.Append("ORDER BY ");
                if (DESC.Count > 0)
                {
                    foreach (String col in DESC)
                    {
                        sql.Append(col).Append(",");
                    }
                    sql = sql.Remove(sql.Length - 1, 1);
                    sql.Append(" DESC ");
                }
                if (ASC.Count > 0)
                {
                    foreach (String col in ASC)
                    {
                        sql.Append(col).Append(",");
                    }
                    sql = sql.Remove(sql.Length - 1, 1);
                    sql.Append(" ASC");
                }
            }
            return sql.ToString();
        }

        private string MakePageSql(int PageID, int PageSize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ").Append(PageSize.ToString()).Append("*");
            sql.Append(" FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ");
            if ((DESC.Count + ASC.Count) < 0)
            {
                sql.Append(queryparam._PrimaryKeyName).Append(" ASC )");
            }
            else
            {
                if (DESC.Count > 0)
                {
                    for (int i = 0; i < DESC.Count; i++)
                    {
                        if (i < (DESC.Count - 1))
                        {
                            sql.Append(DESC[i]).Append(",");
                        }
                        else
                        {
                            sql.Append(DESC[i]);
                        }
                    }
                    sql.Append(" DESC ");
                }
                if (ASC.Count > 0)
                {
                    for (int i = 0; i < ASC.Count; i++)
                    {
                        if (i < (ASC.Count - 1))
                        {
                            sql.Append(ASC[i]).Append(",");
                        }
                        else
                        {
                            sql.Append(ASC[i]);
                        }
                    }
                    sql.Append(" ASC ");
                }
                sql.Append(")");
            }
            sql.Append(" AS RowNumber,* FROM [").Append(queryparam._ObjectTableName).Append("] ");
            if (where != "")
            {
                sql.Append(" WHERE ").Append(where);
            }
            sql.Append(" ) A WHERE RowNumber > ").Append("" + PageSize * (PageID - 1));
            return sql.ToString();
        }

        public IQuery SetUpdateProperty(Exp property)
        {
            updatecolumns.Add(property.Name);
            return this;
        }

        #endregion
    }
}
